import time
from DataTransfer.log import Logger
from DataTransfer.connection import *

logger = Logger(logging_name='log.txt', logging_level='INFO', logger='data.transfer.py').get_log()


# MySQL配置信息
# 日志
# EXEC_DETAIL_FILE = 'exec_detail.txt'
# 日期格式化
DATETIME_FORMAT = '%Y-%m-%d %X'
# 起始时间
Transfer_Condition = "dt<'2017-08-22'"
# 最大行数
Transfer_Rows_Per_Batch = 10000
# 时间？
Sleep_Second_Per_Batch = 0.5


def get_time_string(dt_time):
    """
    获取指定格式的时间字符串
    :param dt_time: 要转换成字符串的时间
    :return: 返回指定格式的字符串
    """
    global DATETIME_FORMAT
    return time.strftime(DATETIME_FORMAT, dt_time)


# def highlight(s):
#     # return '\033[1;31;40;%s' % s
#     return "%s[30;2m%s%s[1m" % (chr(27), s, chr(27))


# def print_warning_message(message):
#     """
#     以红色字体显示消息内容
#     :param message: 消息内容
#     :return: 无返回值
#     """
#     message = str(message)
#     print(highlight('') + "%s[31;1m%s%s[0m" % (chr(27), message, chr(27)))


# def print_info_message(message):
#     """
#     以绿色字体显示消息内容
#     :param message: 消息内容
#     :return: 无返回值
#     """
#     message = str(message)
#     print(highlight('') + "%s[32;1m%s%s[0m" % (chr(27), message, chr(27)))


def mysql_exec(sql_script, sql_param=None):
    """
    执行传入的脚本，返回影响行数
    :param sql_script: 
    :param sql_param: 
    :return: 脚本最后一条语句执行影响行数
    """
    try:
        conn = get_source_connection()
        logger.info("在服务器{0}上执行脚本：{1}".format(
            conn.get_host_info(), sql_script
        ))
        cursor = conn.cursor()
        if sql_param is not None:
            cursor.execute(sql_script, sql_param)
        else:
            cursor.execute(sql_script)
        affect_rows = cursor.rowcount
        conn.commit()
        cursor.close()
        conn.close()
        return affect_rows
    except ZeroDivisionError:
        logger.error("mysql_exec执行出现异常")


def mysql_exec_many(sql_script_list):
    """
    执行传入的脚本，返回影响的行数
    :param sql_script_list: 要执行的脚本List，List中的每个元素为sql_script,sql_param对
    :return: 返回执行每行脚本影响的行数列表
    """
    try:
        conn = get_target_connection()
        exec_result_list = []
        for sql_script, sql_param in sql_script_list:
            logger.info("在服务器{0}上执行脚本：{1}".format(
                conn.get_host_info(), sql_script
            ))
            cursor = conn.cursor()
            if sql_param is not None:
                cursor.execute(sql_script, sql_param)
            else:
                cursor.execute(sql_script)
            affect_rows = cursor.rowcount
            exec_result_list.append("影响行数：{0}".format(affect_rows))
            cursor.close()
        conn.commit()
        conn.close()
        return exec_result_list
    except ZeroDivisionError:
        logger.error("mysql_exec_many执行出现异常")


def mysql_query(sql_script, sql_param=None):
    """
    执行传入的SQL脚本，并返回查询结果
    :param sql_script: 
    :param sql_param: 
    :return: 返回SQL查询结果
    """
    try:
        conn = get_source_connection()
        logger.info("在服务器{0}上执行脚本：{1}".format(
            conn.get_host_info(), sql_script
        ))
        cursor = conn.cursor()
        if sql_param != '':
            cursor.execute(sql_script, sql_param)
        else:
            cursor.execute(sql_script)
        exec_result = cursor.fetchall()
        cursor.close()
        conn.close()
        return exec_result
    except ZeroDivisionError:
        logger.error("mysql_query执行出现异常")


# def get_column_info_list(table_name):
#     """
#
#     :param table_name:
#     :return:
#     """
#     sql_script = """DESC {0}""".format(table_name)
#     column_info_list = []
#     query_result = mysql_query(sql_script=sql_script, sql_param=None)
#     for row in query_result:
#         column_name = row[0]
#         column_key = row[3]
#         column_info = column_name, column_key
#         column_info_list.append(column_info)
#     return column_info_list


def get_id_range():
    """
    按照传入的表获取要删除数据的最大ID、最小ID、删除总行数
    :return: 返回要删除数据的最大ID、最小ID、删除总行数
    """
    # global Transfer_Condition
    global Transfer_Rows_Per_Batch
    sql_script = """SELECT MAX(ID) AS MAX_ID, MIN(ID) AS MIN_ID, COUNT(1) AS Total_Count FROM {0} WHERE {1};""".format(
        Transfer_Source_Table_Name, Transfer_Condition)
    query_result = mysql_query(sql_script=sql_script, sql_param=None)
    max_id, min_id, total_count = query_result[0]
    # 此处有一坑，可能出现total_count不为0 但是max_id 和min_id 为None的情况
    # 因此判断max_id和min_id 是否为NULL
    if (max_id is None) or (min_id is None):
        max_id, min_id, total_count = 0, 0, 0
    return max_id, min_id, total_count


# def check_env():
#     try:
#         source_columns_info_list = get_column_info_list(Transfer_Source_Table_Name)
#         target_columns_info_list = get_column_info_list(Transfer_Target_Table_Name)
#         if len(source_columns_info_list) != len(target_columns_info_list):
#             logger.warning('源表和目标表的列数不对，不满足迁移条件')
#             return False
#         column_count = len(source_columns_info_list)
#         id_flag = False
#         for column_id in range(column_count):
#             source_column_name, source_column_key = source_columns_info_list[column_id]
#             target_column_name, target_column_key = target_columns_info_list[column_id]
#             if source_column_name != target_column_name:
#                 logger.warning('源表和目标表的列名不匹配，不满足迁移条件')
#                 return False
#             if source_column_name.lower() == 'id' \
#                     and source_column_key.lower() == 'pri' \
#                     and target_column_name.lower() == 'id' \
#                     and target_column_key.lower() == 'pri':
#                 id_flag = True
#         if not id_flag:
#             logger.warning("未找到为主键的id列，不满足迁移条件")
#             return False
#         return True
#     except ZeroDivisionError:
#         logger.error("check_env执行出现异常")
#         return False


def main():
    # flag = check_env()
    # if not flag:
    return loop_trans_data()


def trans_data(current_min_id, current_max_id):
    # global Transfer_Rows_Per_Batch
    logger.info("*" * 70)
    copy_data_script = """INSERT INTO {0}.{1} SELECT * FROM {2}.{3} WHERE ID>={4} AND ID<{5} AND {6};""".format(
        Target_DataBase_Name, Transfer_Target_Table_Name, Source_DataBase_Name, Transfer_Source_Table_Name,
        current_min_id, current_max_id, Transfer_Condition)
#     delete_data_script = """DELETE FROM {0} WHERE ID IN (SELECT ID FROM {1} WHERE ID>={2} AND ID<{3}) AND ID>={4}
# AND ID<{5};""".format(Transfer_Source_Table_Name, Transfer_Target_Table_Name, current_min_id, current_max_id,
#                       current_min_id, current_max_id)
    sql_script_list = []
    tem_sql_script = copy_data_script, None
    sql_script_list.append(tem_sql_script)
    # tem_sql_script = delete_data_script, None
    # sql_script_list.append(tem_sql_script)
    exec_result_list = mysql_exec_many(sql_script_list)
    logger.info("执行结果:")
    for item in exec_result_list:
        logger.info(item)


def loop_trans_data():
    max_id, min_id, total_count = get_id_range()
    if min_id == max_id:
        logger.info("无数据需要结转")
        return
    current_min_id = min_id
    # global Transfer_Rows_Per_Batch
    while current_min_id <= max_id:
        current_max_id = current_min_id + Transfer_Rows_Per_Batch
        trans_data(current_min_id, current_max_id)
        current_percent = (current_max_id - min_id) * 100.0 / (max_id - min_id)
        left_rows = max_id - current_max_id
        if left_rows < 0:
            left_rows = 0
        current_percent_str = "%.2f" % current_percent
        info = "当前复制进度{0}/{1},剩余{2},进度为{3}%".format(current_max_id, max_id, left_rows, current_percent_str)
        logger.info(info)
        time.sleep(Sleep_Second_Per_Batch)
        current_min_id = current_max_id
    logger.info("*" * 70)
    logger.info("执行完成")


if __name__ == '__main__':
    main()
